package net.bwie.ads;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class CreateMaterializedView {
    public static void main(String[] args) {
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement()) {

            // 使用execute()方法执行DDL语句
            boolean success = statement.execute(
                    " CREATE MATERIALIZED VIEW ads_cars_out_cnt \n" +
                            " DISTRIBUTED BY HASH(name) BUCKETS 10 \n" +
                            " PROPERTIES (\n" +
                            "   \"replication_num\" = \"1\"\n" +
                            " )\n" +
                            " AS \n" +
                            " SELECT '驶入' as name, round(in_cnt/total*100,2) as zb \n" +
                            " FROM ( \n" +
                            "     SELECT \n" +
                            "         DATE_FORMAT(window_start,'%Y-%m-%d') as time, \n" +
                            "         SUM(in_cnt) as in_cnt, \n" +
                            "         SUM(out_cnt) as out_cnt, \n" +
                            "         SUM(in_cnt+out_cnt) as total \n" +
                            "     FROM dws_sca.dws_summary_cars_log \n" +
                            "     WHERE DATE_FORMAT(window_start, '%Y-%m-%d') = '2025-08-21' \n" +
                            "     GROUP BY DATE_FORMAT(window_start,'%Y-%m-%d') \n" +
                            " ) a1 \n" +
                            " UNION ALL \n" +
                            " SELECT '驶出' as name, round(out_cnt/total*100,2) as zb \n" +
                            " FROM ( \n" +
                            "     SELECT \n" +
                            "         DATE_FORMAT(window_start,'%Y-%m-%d') as time, \n" +
                            "         SUM(in_cnt) as in_cnt, \n" +
                            "         SUM(out_cnt) as out_cnt, \n" +
                            "         SUM(in_cnt+out_cnt) as total \n" +
                            "     FROM dws_sca.dws_summary_cars_log \n" +
                            "     WHERE DATE_FORMAT(window_start, '%Y-%m-%d') = '2025-08-21' \n" +
                            "     GROUP BY DATE_FORMAT(window_start,'%Y-%m-%d') \n" +
                            " ) a2 "
            );





            if (!success) {
                System.out.println("物化视图创建成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接的方法
    private static Connection getConnection() throws SQLException {
        // 为Doris添加更完整的连接参数
        String url = "jdbc:mysql://node102:9030/dws_sca" +
                "?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true&useServerPrepStmts=false&rewriteBatchedStatements=true";
        String username = "root";
        String password = "123456";
        return DriverManager.getConnection(url, username, password);
    }
}
